#！/bin/bash
source /etc/profile
# 手动输入开始日期和结束日期
start_date=$1
end_date=$2
# 定义要执行的SQL
sql="
insert overwrite table ads.ads_continue_login_3_days 
partition(dt='$end_date') 
SELECT
	device_id,
	uid,
	app_v,
	os_type,
	language,
	channel,
	area,
	brand,
	gid,
	count(1) 
FROM
	(
		SELECT
			device_id,
			uid,
			app_v,
			os_type,
			language,
			channel,
			area,
			brand,
			date_sub(dt, row_number () over (
partition by device_id order by dt
				)
			) gid 
		FROM
 			dws.dws_member_start_day_hw
 		WHERE
		       dt >= '$start_date'
		 AND dt <= '$end_date'
	) tmp 
GROUP BY 
	device_id,
	uid,
	app_v,
	os_type,
	language,
	channel,
	area,
	brand,
	gid 
 HAVING count(1) >= 3
"
hive -e "$sql"
